﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;

namespace BanHangCshap.Class
{
    class PhieuNhapXuatSub : Database
    {
        public Guid Id { get; set; }
        public DateTime Ngay { get; set; }
        public string tennd { get; set; }
        public string Ghichu { get; set; }
        public string manhapxuat { get; set; }

        /// <summary>
        /// Lấy các phiếu xuất con
        /// Vì trường hợp đặc biệt và ít lần nên lấy hết (Select *)
        /// </summary>
        /// <param name="manhapxuat">Mã phiếu xuất</param>
        /// <returns>Bảng chứa thông tin cơ bản về các lần xuất con</returns>
        public DataTable PhieuNhapXuatSub_Select(string manhapxuat)
        {
            string sql = @"
                         Select
                                *
                         From
                                PhieuNhapXuatSub pnxs
                         Where
                                manhapxuat ='" + manhapxuat + @"'
                         order by s desc
                            ";
            return getDataTable(sql);
        }

        public DataTable PhieuNhapXuatSub_Select_ChitietPhieunhap(string manhapxuat)
        {
            string sql = @"
                        select
	                        kq1.*,
	                        kq1.slnhapxuat - ISNULL(kq2.tsldaxuat,0) chuagiao
                        from
	                        (
	                            select
		                            hh.mahang,
		                            hh.mahang+'__'+hh.tenhang maten,
                                    hh.tenhang,
		                            ct.dvt,
		                            ct.slchuaquydoi,
		                            ct.dgnhapxuat,
		                            ct.chietkhau,
		                            ct.thanhtien,
		                            ct.slnhapxuat,
		                            hh.dvt dvt0,
                                    ct.slquydoi
	                            from
		                            PhieuNhapXuatCT ct,
		                            Hanghoa hh
	                            where
		                            ct.manhapxuat='" + manhapxuat + @"'
		                            and ct.mahang =hh.mahang
	                        ) kq1
	                        left join
	                        (
	                            select
		                            SUM(s2.slnhapxuat) tsldaxuat, s2.mahang
	                            from
		                            PhieuNhapXuatSub s1
		                            ,PhieuNhapXuatCTSub s2
	                            where
		                            s1.manhapxuat ='" + manhapxuat + @"'
                                    and s1.Id=s2.Id_PhieuNhapXuatSub
	                            group by
		                            s2.mahang
	                        ) kq2 on kq1.mahang =kq2.mahang
                        ";
            return getDataTable(sql);
        }

        /// <summary>
        /// Thêm vào một lần xuất hàng con
        /// </summary>
        /// <returns></returns>
        public bool PhieuNhapXuatSub_Insert()
        {
            string sql = @"
                         INSERT INTO PhieuNhapXuatSub
                               ([Id]
                               ,[Ngay]
                               ,[tennd]
                               ,[Ghichu]
                               ,[manhapxuat])
                         VALUES
                               ('" + Id + @"'
                               ,'" + Ngay.ToString("yyyy/MM/dd") + @"'
                               ,'" + tennd + @"'
                               ,N'" + Ghichu + @"'
                               ,'" + manhapxuat + @"')
                         ";
            return executeQueryTrans(sql);
        }

        public DataTable PhieuNhapXuatCTSub_Select_Hanghoa_In_Hanghoa(string mahang)
        {
            return getDataTable(String.Format("Select * from hanghoa where mahang = '{0}'", mahang));
        }

        public DataTable PhieuNhapXuatCTSub_Select_In0(string manhapxuat)
        {
            string sql = @"
                            select
	                            k.tenkhncc
	                            ,k.diachikhncc
	                            ,k.sodtkhncc
	                            ,nd.hoten
                            from
	                            PhieuNhapXuat p
	                            ,KhachHangNCC k
	                            ,NguoiDung nd
                            where
	                            p.manhapxuat = '" + manhapxuat + @"'
	                            and p.makhncc=k.makhncc
	                            and p.tendn =nd.tendn
                            ";
            return getDataTable(sql);
        }

        /// <summary>
        /// Cần 2 biến mà mới có 1 à
        /// Đi dạy cái rồi tối về hoàn thành luôn hihi ít nữa là ok
        /// </summary>
        /// <param name="Id_PhieuNhapXuatSub"></param>
        /// <returns></returns>
        public DataTable PhieuNhapXuatCTSub_Select_In1(string manhapxuat, Guid Id_PhieuNhapXuatSub)
        {
            string sql = @"
							Select
	                            ps0.manhapxuat
	                            ,ps0.Ngay
	                            ,ps0.Ghichu
	                            ,hh.tenhang
	                            ,ps.slchuaquydoi
	                            ,ps.dvt
	                            ,ps.dgnhapxuat
	                            ,ps.chietkhau
	                            ,ps.thanhtien
	                            ,nd.hoten
	                            --,TongLan.TongtienLan
	                            ,TongCaclan.TongtienCaclan
	                            ,p.tongtienso
	                            ,p.thanhtoan
                                ,p.thanhtoan -TongCaclan.TongtienCaclan Conlai
                            From
	                            PhieuNhapXuatSub ps0
	                            left join
	                            (
								   select
										sp0.manhapxuat
										,SUM(sp1.thanhtien) TongtienCaclan
									from
										PhieuNhapXuatSub sp0
										,PhieuNhapXuatCTSub sp1
									where
										sp0.Id=sp1.Id_PhieuNhapXuatSub
										and sp0.manhapxuat='" + manhapxuat + @"'
									group by
										sp0.manhapxuat
	                            ) TongCaclan on TongCaclan.manhapxuat=ps0.manhapxuat
	                            ,HangHoa hh
	                            ,NguoiDung nd
	                            ,PhieuNhapXuatCTSub ps
	                            /*left join 
	                            (
									select
										sp1.Id_PhieuNhapXuatSub, SUM(sp1.thanhtien) TongtienLan
									from
										PhieuNhapXuatCTSub sp1
									where 
										sp1.Id_PhieuNhapXuatSub='" + Id_PhieuNhapXuatSub + @"'
									group by
										sp1.Id_PhieuNhapXuatSub
	                            ) TongLan on TongLan.Id_PhieuNhapXuatSub=ps.Id_PhieuNhapXuatSub*/
	                            ,PhieuNhapXuat p
                            where
	                            ps0.Id=ps.Id_PhieuNhapXuatSub
	                            and ps.Id_PhieuNhapXuatSub ='" + Id_PhieuNhapXuatSub + @"'
	                            and ps.mahang=hh.mahang
	                            and nd.tendn = ps0.tennd
	                            and ps0.manhapxuat=p.manhapxuat
                            ";
            return getDataTable(sql);
        }

        public DataTable PhieuNhapXuatCTSub_Select_Khachhang(string manhapxuat)
        {
            string sql = @"
                        select
	                        k.tenkhncc
	                        ,k.diachikhncc
	                        ,k.sodtkhncc
	                        ,n.hoten
                            ,p.manhapxuat
                        from
	                        PhieuNhapXuat p
	                        ,KhachHangNCC k
	                        ,NguoiDung n
                        where
	                        p.manhapxuat='" + manhapxuat + @"'
	                        and p.makhncc=k.makhncc
	                        and p.tendn=n.tendn
                            ";
            return getDataTable(sql);
        }

        public bool PhieuNhapxuatCTSub_Update_Trangthaiphieu(string manhapxuat,Guid Id_Trangthaiphieu)
        {
            return executeQueryTrans("update phieunhapxuat set Id_Trangthaiphieu ='" + Id_Trangthaiphieu.ToString() + "' where manhapxuat ='" + manhapxuat + "'");
        }

        public bool PhieunhapxuatSub_Xoa(Guid Id)
        {
            return executeQueryTrans("delete PhieunhapxuatSub where Id ='" + Id.ToString() + "'");
        }
    }

    class PhieuNhapXuatCTSub : Database
    {
        public Guid Id { get; set; }
        public Guid Id_PhieuNhapXuatSub { get; set; }
        public string mahang { get; set; }
        public string dvt { get; set; }
        public float slchuaquydoi { get; set; }
        public float slquydoi { get; set; }
        public float slnhapxuat { get; set; }
        public string chuthich { get; set; }
        public int dgnhapxuat { get; set; }
        public int chietkhau { get; set; }
        public int thanhtien { get; set; }

        public DataTable PhieuNhapXuatCTSub_Select(Guid Id_PhieuNhapXuatSub)
        {
            string sql = @"
                            Select
	                            ps.*,
	                            hh.tenhang
                            From
	                            PhieuNhapXuatCTSub ps
	                            ,HangHoa hh
                            where
	                            ps.Id_PhieuNhapXuatSub ='" + Id_PhieuNhapXuatSub + @"'
	                            and ps.mahang=hh.mahang
                            ";
            return getDataTable(sql);
        }

        public bool PhieuNhapXuatCTSub_Insert()
        {
            string sql = @"
                            INSERT INTO PhieuNhapXuatCTSub
                                       ([Id]
                                       ,[Id_PhieuNhapXuatSub]
                                       ,[mahang]
                                       ,[dvt]
                                       ,[slchuaquydoi]
                                       ,[slquydoi]
                                       ,[slnhapxuat]
                                       ,[dgnhapxuat]
                                       ,[chietkhau]
                                       ,[thanhtien]
                                       )
                                 VALUES
                                       ('" + Id + @"'
                                       ,'" + Id_PhieuNhapXuatSub + @"'
                                       ,'" + mahang + @"'
                                       ,'" + dvt + @"'
                                       ,'" + slchuaquydoi + @"'
                                       ,'" + slquydoi + @"'
                                       ,'" + slnhapxuat + @"'
                                       ,'" + dgnhapxuat + @"'
                                       ,'" + chietkhau + @"'
                                       ,'" + thanhtien + @"')
                                ";
            return executeQueryTrans(sql);
        }
    }
}
